using System;
using System.IO;
using System.Collections;
using System.Globalization;
using System.Threading;
using Office = Microsoft.Office.Core;
using System.Diagnostics;
using System.Reflection;

namespace bOSdotNET.Microsoft.Office
{
	public class ExcelHelper
    {
		public bool KillAllExcelOnQuit = true;

		private static Excel.Application	excelApplication	= null;
		private static Mutex				mxApplication		= new Mutex();	

		private ArrayList excelWorkbook = new ArrayList();
		private static int count=0;
		private static int in_use=0;
		private static Mutex mx= new Mutex();	// Mutex su count e in_use e la lista

		private static object	vk_missing		= System.Reflection.Missing.Value;
		private static object	vk_visible		= true;
		private static object	vk_false		= false;
		private static object	vk_true			= true;
		private bool			vk_app_visible	= false;

		#region Constructor
		/// <summary>
		/// Initializes a new instance  of the <see cref="ExcelHelper"/> class.
		/// </summary>
		public ExcelHelper()
		{
			this.startExcel();
		}

		/// <summary>
		/// Initializes a new instance of the <see cref="ExcelHelper"/> class.
		/// </summary>
		/// <param name="visible">if set to <c>true</c> Excel Application will be [visible].</param>
		public ExcelHelper(bool visible)
		{
			this.vk_app_visible = visible;
			this.startExcel();
		}
		#endregion
		
		#region Start and Stop Excel Application
		/// <summary>
		/// Starts the excel.
		/// </summary>
		private void startExcel()
		{
			mxApplication.WaitOne();

			if( ExcelHelper.excelApplication == null )
			{
				System.Threading.Thread.CurrentThread.CurrentCulture =
					System.Globalization.CultureInfo.CreateSpecificCulture("en-US");

				ExcelHelper.excelApplication = new Excel.ApplicationClass();
				ExcelHelper.excelApplication.Visible = this.vk_app_visible;
				ExcelHelper.excelApplication.DisplayAlerts = false;
			}

			mxApplication.ReleaseMutex();
		}
		

		/// <summary>
		/// Stops the excel.
		/// </summary>
		private void stopExcel()
		{
			mxApplication.WaitOne();

			if( ExcelHelper.excelApplication != null )
			{
				ExcelHelper.excelApplication.Quit();

				//this.excelWorkbook.Close(false,Missing.Value, Missing.Value);
				//System.Runtime.InteropServices.Marshal.ReleaseComObject (this.excelWorkbook);
				//this.excelWorkbook = null;
				//System.Runtime.InteropServices.Marshal.ReleaseComObject (this.excelSheets);
				//this.excelSheets = null;
				System.Runtime.InteropServices.Marshal.ReleaseComObject (ExcelHelper.excelApplication);
				ExcelHelper.excelApplication = null;
				GC.Collect();   //Garbage collection.

				//close all excel processes.
				if ( this.KillAllExcelOnQuit )
				{
					Process[] pProcess; 
					pProcess = System.Diagnostics.Process.GetProcessesByName("Excel");

					int iSize = pProcess.GetUpperBound(0);
					for ( int i=0; i<=iSize;i++)
					{
						pProcess[i].Kill();
					}
				}
			}

			mxApplication.ReleaseMutex();
		}
		#endregion

		#region Macro
		///deprecated. using RunMacro in OfficeHelper class
		/// <summary>
		/// Runs the macro.
		/// </summary>
		/// <param name="oRunArgs">The macro and its parameters in excel formats.</param>
		public void RunMacro(object[] oRunArgs)
		{
			try
			{
				ExcelHelper.excelApplication.GetType().InvokeMember("Run",
					System.Reflection.BindingFlags.Default |
					System.Reflection.BindingFlags.InvokeMethod,
					null, ExcelHelper.excelApplication, oRunArgs);
			}
			catch (System.Exception e) 
			{
				Console.WriteLine(e);
			}
		}

		#endregion

		#region Open File

		/// <summary>
		/// Opens the file.
		/// </summary>
		/// <param name="fileName">Name of the Excel file.</param>
		/// <param name="password">The password.</param>
		/// <param name="bShared">if set to <c>true</c> the excel file is open in shared way.<br>
		/// An excel file copy will be opened. In this way it'll be possible to work on the file multiple instance of.</param>
		/// <returns>
		/// </returns>
		public WorkbookHelper OpenFile(string fileName, string password, bool bShared)
		{
			WorkbookHelper workbook =this.AddWorkbook();

			if( password.Length > 0 )
			{
				workbook.password = password;
			}

			if ( bShared )
			{
				string sFileName2Copy=fileName.Insert(fileName.Length-4,String.Concat("_",Convert.ToString(workbook.Id)));	
				System.IO.File.Copy (fileName, sFileName2Copy,true);

				/*
				Excel_Application.mfs.WaitOne();
				Excel.Workbook tmp=ExcelHelper.excelApplication.Workbooks.Open(
					fileName,
					this.excelWorkbook[id].update_links, 
					this.excelWorkbook[id].read_only,
					this.excelWorkbook[id].format, 
					this.excelWorkbook[id].password,
					this.excelWorkbook[id].write_res_password, 
					this.excelWorkbook[id].ignore_read_only_recommend, 
					this.excelWorkbook[id].origin,
					this.excelWorkbook[id].delimiter, 
					this.excelWorkbook[id].editable, 
					this.excelWorkbook[id].notify, this.excelWorkbook[id].converter, 
					this.excelWorkbook[id].add_to_mru,this.excelWorkbook[id].local,
					this.excelWorkbook[id].corrupt_load);

				tmp.SaveCopyAs(sFileName2Copy);
				tmp.Close(false,ExcelHelper.ExcelHelper,false);
				Excel_Application.mfs.ReleaseMutex();
				*/

				workbook.v_filename = sFileName2Copy;
				workbook.OpenInSharedWay=true;
			}
			else
			{
				workbook.v_filename = fileName;
			}

			try
			{
				workbook.excelWorkbook = ExcelHelper.excelApplication.Workbooks.Open(
					workbook.v_filename.ToString(),
					workbook.update_links, 
					workbook.read_only,
					workbook.format, 
					workbook.password,
					workbook.write_res_password, 
					workbook.ignore_read_only_recommend, 
					workbook.origin,
					workbook.delimiter, 
					workbook.editable, 
					workbook.notify, workbook.converter, 
					workbook.add_to_mru,workbook.local,
					workbook.corrupt_load);

			}
			catch(System.Exception e)
			{
				this.CloseWorkbook(workbook);
				Console.Write(e.Message);
				//this.excelWorkbook[id].CloseFile();
				//this.Decrementa_contatore();
				return null;
			}
			
			return workbook;
		}
		#endregion

		/// <summary>
		/// Closes the workbook. If workbook is opened in shared way the excel file copy created <see cref="OpenFile"/> will be deleted.
		/// </summary>
		/// <param name="workbook2Close">The workbook to close.</param>
		public void CloseWorkbook(WorkbookHelper workbook2Close)
		{
			if ( workbook2Close.excelWorkbook != null) 
			{
				workbook2Close.CloseFile();
			}

			if ( workbook2Close.OpenInSharedWay) 
			{
				File.Delete(workbook2Close.v_filename.ToString());
			}

			mx.WaitOne();
			excelWorkbook.Remove (workbook2Close);
			in_use--;
			if (in_use==0)
			{
				ExcelHelper.count=0;
				this.stopExcel();
			}
			mx.ReleaseMutex();
		}

		/// <summary>
		/// Adds a new workbook to workbook list. The workbook id will be created.
		/// </summary>
		/// <returns></returns>
		private WorkbookHelper AddWorkbook()
		{
			int id;

			mx.WaitOne();
			in_use++;
			id=++ExcelHelper.count;

			WorkbookHelper workbook = new WorkbookHelper(id);

			excelWorkbook.Add (workbook);

			mx.ReleaseMutex();
			
			return workbook;
		}

	}// end Excel_Applciation

	public class WorkbookHelper
	{
		//Workbook Id. 
		public int Id;

		public bool OpenInSharedWay = false;

		internal Excel.Workbook	excelWorkbook		= null;
		private Excel.Worksheet	excelActiveSheet	= null;
		private Excel.Sheets	excelSheets			= null;
		
		private static object missing	= System.Reflection.Missing.Value;
		private static object visible	= true;
		private static object v_false	= false;
		private static object v_true	= true;

		public object	v_filename;

		#region OPEN WORKBOOK VARIABLES
		public object update_links					= 0;
		public object read_only						= v_false;
		public object format						= 1;
		public object password						= missing;
		public object write_res_password			= missing;
		public object ignore_read_only_recommend	= v_true;
		public object origin						= missing;
		public object delimiter						= missing;
		public object editable						= v_false;
		public object notify						= v_false;
		public object converter						= missing;
		public object add_to_mru					= v_false;
		public object local							= v_false;
		public object corrupt_load					= v_false;
		#endregion

		#region CLOSE WORKBOOK VARIABLES
		private object v_save_changes	= v_false;
		private object v_route_workbook = v_false;
		#endregion

		public WorkbookHelper(int i)
		{
			this.Id=i;
		}

		public void CloseFile()
		{
			if (this.excelSheets!=null) 
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject (this.excelSheets);
				this.excelSheets = null;
			}

			if (this.excelActiveSheet!=null) 
			{
				System.Runtime.InteropServices.Marshal.ReleaseComObject (this.excelActiveSheet);
				this.excelActiveSheet = null;
			}

			if ( excelWorkbook != null)
			{
				this.excelWorkbook.Close( v_save_changes, this.v_filename, v_route_workbook );
				//this.excelWorkbook.Close(false,Missing.Value, Missing.Value);
				System.Runtime.InteropServices.Marshal.ReleaseComObject (this.excelWorkbook);
				this.excelWorkbook = null;
			}

		}

		public bool SetWorksheet(int num)
		{
			if ( this.excelSheets == null )
			{
				this.excelSheets = this.excelWorkbook.Sheets;
			}

			if( this.excelSheets != null )
			{
				Excel.Worksheet sheet = (Excel.Worksheet)this.excelSheets.get_Item((object)num);
				excelActiveSheet=sheet;
				return true;
			}
			return false;
		}

		public bool SetWorksheet(string worksheetName)
		{
			if ( this.excelSheets == null )
			{
				this.excelSheets = this.excelWorkbook.Sheets;
			}
			
			if( this.excelSheets != null )
			{
				// Step thru the worksheet collection and see if ATP sheet is
				// available. If found return true;
				for( int i=1; i<=this.excelSheets.Count; i++ )
				{
					Excel.Worksheet sheet = (Excel.Worksheet)this.excelSheets.get_Item((object)i);
					if( sheet.Name.Equals(worksheetName) )
					{
						sheet.Activate();
						excelActiveSheet=sheet;
						return true;
					}
				}
			}

			return false;
		}



		public string[,] GetCells(int iRowInf, int iRowSup, int iColInf, int iColSup)
		{
			int iSizeRow = iRowSup-iRowInf+1;
			int iSizeCol = iColSup- iColInf+1;

			string[,] arrayS = new string[iSizeRow, iSizeCol];

			Excel.Range myCells = (Excel.Range) this.excelActiveSheet.Cells;
			
			Excel.Range myCell = null;

			for ( int i=0; i<iSizeRow; i++)
			{
				for ( int j=0; j<iSizeCol;j++)
				{
					myCell = (Excel.Range) myCells[iRowInf+i, iColInf+j];
					arrayS[i, j] = myCell.Text.ToString();
					System.Runtime.InteropServices.Marshal.ReleaseComObject (myCell);
					myCell = null;
				}
			}

			System.Runtime.InteropServices.Marshal.ReleaseComObject (myCells);
			myCells = null;

			return arrayS;
		}


		public void WriteCell(int row, int column, object val)
		{
			Excel.Range myCells = (Excel.Range) this.excelActiveSheet.Cells;
			Excel.Range myCell = (Excel.Range) myCells[row, column];
			
			myCell.set_Value (Type.Missing, val);
 
			System.Runtime.InteropServices.Marshal.ReleaseComObject (myCells);
			myCells = null;
			System.Runtime.InteropServices.Marshal.ReleaseComObject (myCell);
			myCell = null;
		}

		public string ReadCell(int row, int column)
		{
			Excel.Range myCells = (Excel.Range) this.excelActiveSheet.Cells;
			Excel.Range myCell = (Excel.Range) myCells[row, column];
			
			string sReturn = myCell.Text.ToString();
 
			System.Runtime.InteropServices.Marshal.ReleaseComObject (myCells);
			myCells = null;
			System.Runtime.InteropServices.Marshal.ReleaseComObject (myCell);
			myCell = null;

			return sReturn;
		}

		public void WriteVerticalCells(int row, int column, object[]val)
		{
			Excel.Range myCells = (Excel.Range) this.excelActiveSheet.Cells;
			
			Excel.Range myCell = null;

			int iSize = val.GetUpperBound(0);
			for ( int i=0; i<=iSize; i++)
			{
				myCell = (Excel.Range) myCells[row+i, column];
				myCell.set_Value (Type.Missing, val[i]);

				System.Runtime.InteropServices.Marshal.ReleaseComObject (myCell);
				myCell = null;
			}

			System.Runtime.InteropServices.Marshal.ReleaseComObject (myCells);
			myCells = null;
		}

		public string[,] ReadCells(string inizio, string fine)
		{
			Excel.Range myrange = this.excelActiveSheet.get_Range(inizio,fine);
			System.Array arr= (System.Array)myrange.Cells.Value2;
			string[,] arrayS = this.ConvertToStringArray(arr);
			System.Runtime.InteropServices.Marshal.ReleaseComObject (myrange);
			myrange = null;
			return arrayS;
		}		
		
		public void WriteCells(string inizio,string fine, object[,] val)
		{
			/*int iSizeRow = iRowSup-iRowInf+1;
			int iSizeCol = iColSup- iColInf+1;*/
			Excel.Range myrange= this.excelActiveSheet.get_Range(inizio,fine);
			
			for (int i=0; i<myrange.Rows.Count;i++)
				for (int j=0; j<myrange.Columns.Count;j++)
				{
					myrange.Cells[i+1,j+1]=val[i,j];
				}
			
			System.Runtime.InteropServices.Marshal.ReleaseComObject (myrange);
			myrange = null;
				
		}

		public string ReadCell(string cella)
		{
			Excel.Range myCell = this.excelActiveSheet.get_Range(cella,Type.Missing);
			//Excel.Range myCell = (Excel.Range) myCells[row, column];
			
			string sReturn = myCell.Value2.ToString();
 
			/*System.Runtime.InteropServices.Marshal.ReleaseComObject (myCells);
			myCells = null;*/
			System.Runtime.InteropServices.Marshal.ReleaseComObject (myCell);
			myCell = null;

			return sReturn;
		}

		public void WriteCell(string cella, int val)
		{
			Excel.Range myCell =this.excelActiveSheet.get_Range(cella, Type.Missing);
			myCell.Value2=val;
			
			System.Runtime.InteropServices.Marshal.ReleaseComObject (myCell);
			myCell = null;
		}

		private string[,] ConvertToStringArray(System.Array values)
		{
			string[,] newArray = new string[values.GetUpperBound(0),values.GetUpperBound(1)];

			int indexRow = 0;
			int indexCol=0;
			for ( int i = values.GetLowerBound(0); i <= values.GetUpperBound(0); i++ )
			{
				indexCol=0;
				for ( int j = values.GetLowerBound(1); j <= values.GetUpperBound(1); j++ )
				{
					if(values.GetValue(i,j)==null)
					{
						newArray[indexRow,indexCol]="";
					}
					else
					{
						newArray[indexRow,indexCol]=(string)values.GetValue(i,j).ToString();
					}
					indexCol++;
				}
				indexRow++;
			}
			return newArray;
		}

		
	}

}//End namespace
